Connecting MySQL with Node.js Applications

Course: MySQL Training

Topic: Connecting MySQL with Node.js Applications

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

Academy Website: Visit JustAcademy

1. Introduction to Connecting MySQL with Node.js Applications

Node.js is a JavaScript runtime environment that allows developers to build server-side applications, web APIs, backend services, and real-time applications. MySQL is a relational database management system used to store and manage structured information in tables.

Connecting MySQL with Node.js allows applications to store, retrieve, update, and delete database records using JavaScript. This integration is commonly used in e-commerce platforms, employee management systems, customer relationship management applications, inventory systems, and online learning platforms.

For example, when a customer registers on a website, a Node.js backend can validate the submitted information and insert the customer details into a MySQL database. When the customer logs in later, the application can retrieve the relevant account information from the database.

1.1 What Is MySQL Connectivity in Node.js?

MySQL connectivity in Node.js is the process of establishing communication between a Node.js application and a MySQL server through a database driver or connector. The driver provides methods for creating connections, executing SQL queries, processing results, and handling database errors.

The typical connectivity process includes:

1. Install Node.js and npm.
2. Install MySQL Server and verify that it is running.
3. Create a MySQL database and an application-specific database user.
4. Install a compatible Node.js MySQL driver.
5. Configure the database hostname, port, username, password, and database name.
6. Create a connection or connection pool.
7. Execute SQL statements from the application.
8. Process query results and handle errors.
9. Release connections and shut down database resources appropriately.

1.2 Why Connect Node.js with MySQL?

• Persistent data storage: Save application data beyond the lifetime of a Node.js process.
• Dynamic applications: Display information retrieved from database tables.
• CRUD operations: Create, read, update, and delete records.
• API development: Build REST APIs that use MySQL as a data store.
• Business automation: Automate order processing, reporting, and record management.
• Data consistency: Use database constraints and transactions to maintain valid records.
• Scalability: Use connection pooling and efficient SQL queries for concurrent requests.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand how Node.js communicates with MySQL.
• Install and configure the MySQL2 package.
• Create a MySQL database, table, and application user.
• Connect Node.js to MySQL using a connection object.
• Execute SELECT, INSERT, UPDATE, and DELETE queries.
• Use asynchronous programming with async and await.
• Use parameterized queries to reduce SQL injection risks.
• Understand connection pooling and connection lifecycle management.
• Manage database transactions and handle exceptions.
• Build a basic Express API connected to MySQL.
• Apply security, performance, and troubleshooting practices.

3. Understanding the Node.js and MySQL Architecture

A Node.js application typically contains the application logic, routes, services, and database access code. A MySQL driver communicates with the MySQL server and returns query results to the application.

3.1 Connectivity Flow

Client or Browser
       |
       v
Node.js Application / Express API
       |
       v
Application Service or Database Module
       |
       v
MySQL2 Driver
       |
       v
MySQL Server
       |
       v
Database Tables
       |
       v
Query Results Returned to Node.js
       |
       v
HTTP Response Returned to Client

The browser should normally communicate with the Node.js backend rather than connecting directly to MySQL. This keeps database credentials on the server and allows the backend to enforce authentication, authorization, validation, and business rules.

3.2 Role of the MySQL Driver

A database driver provides the functions required to establish connections and communicate with the database server. It also supports query execution, parameter binding, result processing, and error reporting.

In this topic, the examples use MySQL2, a popular Node.js MySQL driver that supports prepared statements, promises, and connection pooling. Read the MySQL2 documentation.

4. Prerequisites for Connecting Node.js with MySQL

Before developing the application, make sure the necessary software and database resources are available.

4.1 Verify Node.js Installation

Run the following commands in a terminal:

node --version
npm --version

The commands display the installed Node.js and npm versions. If either command is not recognized, install Node.js and ensure the installation directory is available through your system's PATH.

4.2 Verify MySQL Server

Open your MySQL client or use the command-line client to verify that the server is available:

mysql -h localhost -P 3306 -u root -p

This command attempts to connect to a MySQL server running locally on port 3306. Enter the password when prompted. Your environment may use a different hostname or port.

5. Creating a Node.js Project

It is good practice to create a separate project directory for your application. This directory will contain JavaScript files, package information, and any configuration required for database connectivity.

5.1 Create the Project Directory

mkdir node-mysql-app
cd node-mysql-app

5.2 Initialize the Project

npm init -y

This command creates a package.json file containing the project's package information and dependency configuration.

5.3 Install MySQL2

npm install mysql2

The MySQL2 package provides the database driver. Its promise interface allows you to use async and await, which can make asynchronous database code easier to read and maintain.

5.4 Install Express for API Development

npm install express

Express is a Node.js web framework commonly used to build HTTP endpoints. It is not required for basic MySQL connectivity, but it is useful when developing an API that accesses the database.

6. Preparing the MySQL Database

Create a database and a table before writing the Node.js connection code. You can execute these statements through MySQL Workbench or another authorized MySQL client.

6.1 Create a Database

CREATE DATABASE company_db;

The statement creates a database named company_db. If the database may already exist, use CREATE DATABASE IF NOT EXISTS company_db;.

6.2 Select the Database

USE company_db;

The USE statement selects the database for subsequent statements in the current MySQL session.

6.3 Create an Employees Table

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    department VARCHAR(80),
    salary DECIMAL(10,2),
    joining_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The table stores employee information. The employee ID is generated automatically, the email must be unique, and the salary uses DECIMAL to represent monetary values more appropriately than floating-point types.

6.4 Insert Sample Records

INSERT INTO employees
(employee_name, email, department, salary, joining_date)
VALUES
('Aarav Sharma', '[email protected]', 'IT', 55000.00, '2025-01-15'),
('Priya Verma', '[email protected]', 'HR', 48000.00, '2025-02-10'),
('Rohan Mehta', '[email protected]', 'Finance', 60000.00, '2025-03-05');

These sample records are useful for testing SELECT queries. Avoid repeatedly executing the same insert statements without checking for duplicate email values.

7. Creating a Dedicated MySQL Application User

Applications should normally connect using a dedicated MySQL account rather than an administrator account. A dedicated user makes it possible to limit access to the databases and operations the application actually needs.

7.1 Create the Application User

CREATE USER 'node_app'@'localhost'
IDENTIFIED BY 'Replace_With_A_Strong_Password';

Replace the example password with a securely managed credential. The localhost host definition is intended for matching local connections. A remote deployment may require a different, restricted account host definition and suitable network configuration.

7.2 Grant Required Permissions

GRANT SELECT, INSERT, UPDATE, DELETE
ON company_db.*
TO 'node_app'@'localhost';

These permissions allow basic data retrieval and modification in the specified database. Grant additional permissions only when required by the application. A runtime application generally should not have broad administrative privileges.

7.3 Verify the User's Permissions

SHOW GRANTS FOR 'node_app'@'localhost';

This command displays the permissions assigned to the account. If the account cannot connect or execute a query, verify the username, host definition, password, and privileges.

8. Establishing a Basic MySQL Connection

The MySQL2 package provides createConnection() for creating a direct database connection. This approach is useful for simple scripts, learning examples, and operations that need a single connection.

8.1 Basic Connection Example

const mysql = require("mysql2");

const connection = mysql.createConnection({
    host: "localhost",
    port: 3306,
    user: "node_app",
    password: process.env.MYSQL_PASSWORD,
    database: "company_db"
});

connection.connect((error) => {
    if (error) {
        console.error("Unable to connect to MySQL.");
        return;
    }

    console.log("Connected to MySQL successfully.");

    connection.end((closeError) => {
        if (closeError) {
            console.error("Error closing the connection.");
        }
    });
});

The example uses a callback-based API. The password is read from an environment variable instead of being hardcoded in the source file. Make sure that MYSQL_PASSWORD is configured before running the program.

8.2 Explanation of Connection Parameters

8.3 Direct Connection Versus Connection Pool

9. Using the Promise-Based MySQL2 API

The promise interface of MySQL2 supports async and await. This makes it easier to write asynchronous database operations without deeply nested callbacks.

9.1 Import the Promise Interface

const mysql = require("mysql2/promise");

This import provides promise-based connection and query methods. The following examples use CommonJS syntax, which works with a standard Node.js project configured for CommonJS.

9.2 Connect Using async and await

const mysql = require("mysql2/promise");

async function main() {
    let connection;

    try {
        connection = await mysql.createConnection({
            host: "localhost",
            port: 3306,
            user: "node_app",
            password: process.env.MYSQL_PASSWORD,
            database: "company_db"
        });

        console.log("Connected to MySQL successfully.");
    } catch (error) {
        console.error("Database connection failed.");
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

main();

The await keyword pauses the asynchronous function until the promise settles. The finally block ensures that an established connection is closed after the operation completes.

9.3 Benefits of async and await

• Makes asynchronous code easier to read.
• Allows standard try/catch/finally error handling.
• Reduces deeply nested callback structures.
• Helps organize sequential database operations.
• Works well with promise-based connection and pool methods.

10. Executing SELECT Queries

The SELECT statement retrieves data from MySQL tables. Node.js can execute a SELECT query and use the returned rows to create an API response, generate a report, or perform further application logic.

10.1 Retrieve All Employees

const mysql = require("mysql2/promise");

async function getEmployees() {
    const connection = await mysql.createConnection({
        host: "localhost",
        user: "node_app",
        password: process.env.MYSQL_PASSWORD,
        database: "company_db"
    });

    try {
        const [rows] = await connection.execute(
            "SELECT employee_id, employee_name, email, department FROM employees"
        );

        console.log(rows);
        return rows;
    } finally {
        await connection.end();
    }
}

getEmployees().catch(() => {
    console.error("Unable to retrieve employees.");
});

In the promise API, the query result commonly includes an array containing the returned rows and additional result metadata. The example uses array destructuring to obtain the rows.

10.2 Retrieve a Single Employee by ID

async function getEmployeeById(employeeId) {
    const connection = await mysql.createConnection({
        host: "localhost",
        user: "node_app",
        password: process.env.MYSQL_PASSWORD,
        database: "company_db"
    });

    try {
        const [rows] = await connection.execute(
            "SELECT employee_id, employee_name, email, department FROM employees WHERE employee_id = ?",
            [employeeId]
        );

        if (rows.length === 0) {
            return null;
        }

        return rows[0];
    } finally {
        await connection.end();
    }
}

The query uses a placeholder for the employee ID. The function returns the first matching row or null if no employee is found.

10.3 Filter and Sort Results

const [rows] = await connection.execute(
    `SELECT employee_id, employee_name, salary
     FROM employees
     WHERE department = ?
     ORDER BY salary DESC`,
    ["IT"]
);

This query retrieves employees from the IT department and sorts them by salary in descending order. The department value is supplied separately from the SQL statement.

11. Inserting Data with INSERT

The INSERT statement creates new records in a table. Use parameterized queries so that the supplied values are not treated as SQL syntax.

11.1 Insert a New Employee

const mysql = require("mysql2/promise");

async function addEmployee(name, email, department, salary) {
    const connection = await mysql.createConnection({
        host: "localhost",
        user: "node_app",
        password: process.env.MYSQL_PASSWORD,
        database: "company_db"
    });

    try {
        const [result] = await connection.execute(
            `INSERT INTO employees
             (employee_name, email, department, salary)
             VALUES (?, ?, ?, ?)`,
            [name, email, department, salary]
        );

        return result.insertId;
    } finally {
        await connection.end();
    }
}

addEmployee("Neha Kapoor", "[email protected]", "Marketing", 52000.00)
    .then((id) => console.log("New employee ID:", id))
    .catch(() => console.error("Unable to add employee."));

The function inserts the supplied employee details and returns the generated ID when an auto-increment ID is available. The unique constraint on the email column prevents duplicate email values.

11.2 Understanding the Insert Result

The exact result properties depend on the statement and driver behavior. Check the documentation for the operation being performed.

12. Updating Records with UPDATE

The UPDATE statement modifies existing database records. A WHERE clause should identify the intended rows so that the operation does not unintentionally change every row in the table.

12.1 Update an Employee's Department

const [result] = await connection.execute(
    "UPDATE employees SET department = ? WHERE employee_id = ?",
    ["Engineering", 1]
);

console.log("Rows affected:", result.affectedRows);

The query updates the department for the employee whose ID is 1. The affectedRows value can help the application determine whether the statement affected a row, although its interpretation can depend on the statement and driver configuration.

12.2 Update Multiple Columns

const [result] = await connection.execute(
    `UPDATE employees
     SET department = ?, salary = ?
     WHERE employee_id = ?`,
    ["Engineering", 65000.00, 1]
);

Validate input values and confirm the target employee before executing an update. If the application needs to distinguish a missing record from a record whose values did not change, use a suitable verification strategy.

13. Deleting Records with DELETE

The DELETE statement removes records from a table. Because deletion may be difficult to reverse without a backup or recovery mechanism, applications should validate the target and business rules before performing the operation.

13.1 Delete an Employee by ID

const [result] = await connection.execute(
    "DELETE FROM employees WHERE employee_id = ?",
    [3]
);

console.log("Rows deleted:", result.affectedRows);

The query deletes the employee with ID 3. The application should confirm that the supplied ID is valid and that the user is authorized to perform the deletion.

13.2 DELETE Best Practices

• Use a WHERE clause to select the intended record or records.
• Validate the supplied identifier.
• Check foreign-key relationships and dependent data.
• Consider soft deletion when records must remain available for audit or recovery.
• Use transactions when deletion is part of a multi-step operation.
• Maintain suitable backups and recovery procedures.

14. Parameterized Queries and SQL Injection Prevention

SQL injection is a security vulnerability that occurs when untrusted input changes the structure or meaning of an SQL statement. Parameterized queries help prevent this by keeping SQL instructions separate from the values supplied by the application.

14.1 Unsafe Query Example

const email = userInput;

const sql =
    "SELECT * FROM employees WHERE email = '" + email + "'";

Building SQL by concatenating untrusted input can allow the input to alter the query. This pattern should not be used for user-supplied values.

14.2 Safe Query Using MySQL2

const [rows] = await connection.execute(
    "SELECT employee_id, employee_name FROM employees WHERE email = ?",
    [userInput]
);

The placeholder is bound to the supplied email value. MySQL2's execute() method supports prepared statements for parameterized SQL. Read the MySQL2 documentation.

14.3 Parameterized Queries for CRUD Operations

// SELECT
await connection.execute(
    "SELECT * FROM employees WHERE employee_id = ?",
    [1]
);

// INSERT
await connection.execute(
    "INSERT INTO employees (employee_name, email) VALUES (?, ?)",
    ["Kabir Shah", "[email protected]"]
);

// UPDATE
await connection.execute(
    "UPDATE employees SET department = ? WHERE employee_id = ?",
    ["Sales", 2]
);

// DELETE
await connection.execute(
    "DELETE FROM employees WHERE employee_id = ?",
    [4]
);

Use the driver's parameter-binding mechanism for data values. SQL identifiers such as table names and column names generally cannot be supplied as ordinary value placeholders. If identifiers must vary, select them from a trusted allowlist.

15. Connection Pooling in Node.js

Connection pooling maintains a reusable collection of database connections. Instead of creating a new connection for every query or HTTP request, the application can reuse connections already established with the MySQL server.

15.1 Why Use a Connection Pool?

• Reduces repeated connection-establishment overhead.
• Can improve latency in applications that frequently access the database.
• Limits the number of simultaneous connections created by a pool.
• Provides a central mechanism for connection reuse.
• Supports concurrent database access within configured resource limits.

15.2 Creating a Connection Pool

const mysql = require("mysql2/promise");

const pool = mysql.createPool({
    host: "localhost",
    port: 3306,
    user: "node_app",
    password: process.env.MYSQL_PASSWORD,
    database: "company_db",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

module.exports = pool;

The pool creates connections on demand up to its configured limit. The connection limit should be selected according to application concurrency, MySQL capacity, and the number of application instances.

15.3 Executing a Query Through the Pool

const pool = require("./db");

async function getEmployees() {
    const [rows] = await pool.execute(
        "SELECT employee_id, employee_name, department FROM employees"
    );

    return rows;
}

getEmployees()
    .then(rows => console.log(rows))
    .catch(() => console.error("Unable to retrieve employees."));

For routine queries, using pool.execute() is convenient because the pool manages connection acquisition and release for the operation.

15.4 Pool Configuration Options

15.5 Releasing Manually Acquired Connections

When a connection is acquired directly from the pool, it must be released when the work is finished.

const connection = await pool.getConnection();

try {
    const [rows] = await connection.execute(
        "SELECT employee_id, employee_name FROM employees"
    );

    console.log(rows);
} finally {
    connection.release();
}

The release operation returns the connection to the pool for reuse. Do not call connection.end() on a borrowed pooled connection as a substitute for releasing it.

16. Using Transactions in Node.js

A transaction groups related database operations into one logical unit. Transactions are useful when several changes must succeed together to maintain data consistency.

16.1 Understanding ACID Properties

16.2 Example Transaction

const connection = await pool.getConnection();

try {
    await connection.beginTransaction();

    await connection.execute(
        "UPDATE accounts SET balance = balance - ? WHERE account_id = ?",
        [100, 1]
    );

    await connection.execute(
        "UPDATE accounts SET balance = balance + ? WHERE account_id = ?",
        [100, 2]
    );

    await connection.commit();
    console.log("Transaction completed successfully.");
} catch (error) {
    await connection.rollback();
    console.error("Transaction failed.");
} finally {
    connection.release();
}

This example illustrates a transfer between two accounts. A production implementation must validate account balances, verify affected rows, handle missing accounts, and enforce the business rules. It should also handle rollback failures appropriately. Both updates must execute on the same transaction connection.

16.3 Transaction Best Practices

• Use a single connection for all operations within a transaction.
• Commit only after all required operations succeed.
• Roll back changes when a transaction fails.
• Keep transactions short to reduce lock contention.
• Validate affected rows and business rules.
• Handle deadlocks and transient failures with a controlled retry strategy.
• Always release manually acquired pooled connections.

17. Connecting MySQL with Express.js

Express.js is a Node.js web framework used to build HTTP applications and APIs. It can receive client requests, validate input, execute database operations, and return JSON responses.

17.1 Suggested Project Structure

node-mysql-app/
|-- db.js
|-- server.js
|-- package.json
|-- routes/
|   |-- employees.js
|-- controllers/
|   |-- employeeController.js
|-- services/
|   |-- employeeService.js

This structure is an example rather than a requirement. Small projects can use fewer files, while larger applications benefit from separating database configuration, routes, controllers, and business logic.

17.2 Create the Database Pool

Create a file named db.js:

const mysql = require("mysql2/promise");

const pool = mysql.createPool({
    host: process.env.MYSQL_HOST || "localhost",
    port: Number(process.env.MYSQL_PORT || 3306),
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE || "company_db",
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

module.exports = pool;

Configure the required environment variables before starting the application. The pool is created once and reused across requests rather than being created anew for every route.

17.3 Create an Express Server

Create a file named server.js:

const express = require("express");
const pool = require("./db");

const app = express();
app.use(express.json());

app.get("/employees", async (req, res) => {
    try {
        const [rows] = await pool.execute(
            "SELECT employee_id, employee_name, email, department FROM employees"
        );

        res.json(rows);
    } catch (error) {
        console.error("Unable to retrieve employees.");
        res.status(500).json({
            message: "Unable to retrieve employees."
        });
    }
});

const PORT = process.env.PORT || 3000;

app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
});

This endpoint retrieves employee records and returns them as JSON. A production application should add authentication, authorization, input validation, pagination, and appropriate centralized error handling. Detailed database errors should not be returned to clients.

17.4 Test the API Endpoint

Start the Node.js server and send a GET request to:

http://localhost:3000/employees

If the server and database are configured correctly, the endpoint returns an array of employee records in JSON format. If the connection fails, inspect the server logs and database configuration.

18. Implementing CRUD Operations in an API

CRUD stands for Create, Read, Update, and Delete. These operations are commonly exposed through REST API endpoints.

18.1 Create an Employee Endpoint

app.post("/employees", async (req, res) => {
    const { employee_name, email, department, salary } = req.body;

    if (!employee_name || !email) {
        return res.status(400).json({
            message: "Employee name and email are required."
        });
    }

    try {
        const [result] = await pool.execute(
            `INSERT INTO employees
             (employee_name, email, department, salary)
             VALUES (?, ?, ?, ?)`,
            [
                employee_name,
                email,
                department ?? null,
                salary ?? null
            ]
        );

        res.status(201).json({
            message: "Employee created successfully.",
            employee_id: result.insertId
        });
    } catch (error) {
        if (error.code === "ER_DUP_ENTRY") {
            return res.status(409).json({
                message: "An employee with this email already exists."
            });
        }

        console.error("Unable to create employee.");
        res.status(500).json({
            message: "Unable to create employee."
        });
    }
});

This route demonstrates basic input validation, parameterized SQL, duplicate-entry handling, and an appropriate success status. A real application should also validate email format, salary range, data types, request size, and user permissions.

18.2 Retrieve One Employee

app.get("/employees/:id", async (req, res) => {
    const employeeId = Number(req.params.id);

    if (!Number.isInteger(employeeId) || employeeId <= 0) {
        return res.status(400).json({
            message: "Invalid employee ID."
        });
    }

    try {
        const [rows] = await pool.execute(
            `SELECT employee_id, employee_name, email, department
             FROM employees
             WHERE employee_id = ?`,
            [employeeId]
        );

        if (rows.length === 0) {
            return res.status(404).json({
                message: "Employee not found."
            });
        }

        res.json(rows[0]);
    } catch (error) {
        console.error("Unable to retrieve employee.");
        res.status(500).json({
            message: "Unable to retrieve employee."
        });
    }
});

18.3 Update an Employee

app.patch("/employees/:id", async (req, res) => {
    const employeeId = Number(req.params.id);
    const { department } = req.body;

    if (!Number.isInteger(employeeId) || employeeId <= 0) {
        return res.status(400).json({
            message: "Invalid employee ID."
        });
    }

    if (typeof department !== "string" || !department.trim()) {
        return res.status(400).json({
            message: "A valid department is required."
        });
    }

    try {
        const [result] = await pool.execute(
            "UPDATE employees SET department = ? WHERE employee_id = ?",
            [department.trim(), employeeId]
        );

        if (result.affectedRows === 0) {
            return res.status(404).json({
                message: "Employee not found or no row was changed."
            });
        }

        res.json({
            message: "Employee updated successfully."
        });
    } catch (error) {
        console.error("Unable to update employee.");
        res.status(500).json({
            message: "Unable to update employee."
        });
    }
});

Depending on driver settings, an update that sets a column to its existing value may report no changed rows. If the API must distinguish that case from a missing record, perform an appropriate existence check.

18.4 Delete an Employee

app.delete("/employees/:id", async (req, res) => {
    const employeeId = Number(req.params.id);

    if (!Number.isInteger(employeeId) || employeeId <= 0) {
        return res.status(400).json({
            message: "Invalid employee ID."
        });
    }

    try {
        const [result] = await pool.execute(
            "DELETE FROM employees WHERE employee_id = ?",
            [employeeId]
        );

        if (result.affectedRows === 0) {
            return res.status(404).json({
                message: "Employee not found."
            });
        }

        res.status(204).end();
    } catch (error) {
        console.error("Unable to delete employee.");
        res.status(500).json({
            message: "Unable to delete employee."
        });
    }
});

In production, require appropriate authorization before allowing deletion. Consider audit logging, foreign-key constraints, and soft deletion if records must be retained.

19. Managing Configuration and Credentials

Database credentials should not be hardcoded in source files or exposed to browser-side JavaScript. Use environment variables or a secrets-management system to supply connection configuration.

19.1 Example Environment Variables

MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_USER=node_app
MYSQL_PASSWORD=your_development_password
MYSQL_DATABASE=company_db

This is an example of configuration names, not a file that should contain production secrets in source control. In deployment, configure secrets through a suitable secure mechanism.

19.2 Using a .env File for Local Development

You can use the dotenv package to load local environment configuration.

npm install dotenv

At the beginning of the application entry point, load the configuration:

require("dotenv").config();

const pool = require("./db");

A local .env file may contain development settings, but it should be excluded from version control. Use a placeholder file such as .env.example to document required variable names without including real credentials.

19.3 Security Guidelines

• Never commit production passwords or database URLs containing secrets.
• Use a dedicated MySQL application account.
• Grant only the permissions required by the application.
• Restrict network access to authorized application hosts.
• Use TLS with appropriate certificate verification for remote connections when required.
• Rotate credentials according to organizational policy.
• Do not expose detailed database errors to API clients.
• Do not store database credentials in browser JavaScript.

20. Asynchronous Programming and Error Handling

Node.js uses an asynchronous programming model. Database operations may take time because they depend on the database server and network, so applications commonly use callbacks or promises to handle results without blocking the entire event loop while waiting.

20.1 Callback-Based Query

const mysql = require("mysql2");

const connection = mysql.createConnection({
    host: "localhost",
    user: "node_app",
    password: process.env.MYSQL_PASSWORD,
    database: "company_db"
});

connection.query(
    "SELECT employee_id, employee_name FROM employees",
    (error, rows) => {
        if (error) {
            console.error("Query failed.");
            connection.end();
            return;
        }

        console.log(rows);
        connection.end();
    }
);

20.2 Promise-Based Query

const mysql = require("mysql2/promise");

async function getEmployeeCount() {
    const connection = await mysql.createConnection({
        host: "localhost",
        user: "node_app",
        password: process.env.MYSQL_PASSWORD,
        database: "company_db"
    });

    try {
        const [rows] = await connection.execute(
            "SELECT COUNT(*) AS total FROM employees"
        );

        return rows[0].total;
    } finally {
        await connection.end();
    }
}

getEmployeeCount()
    .then(total => console.log("Total employees:", total))
    .catch(() => console.error("Unable to count employees."));

20.3 Error Handling Best Practices

• Use try/catch around awaited database operations.
• Use finally to clean up resources.
• Handle expected errors, such as duplicate unique values, separately where appropriate.
• Log useful diagnostic information securely.
• Return appropriate HTTP status codes from APIs.
• Avoid returning SQL statements, passwords, or internal database details to clients.
• Do not ignore rejected promises.

21. Common MySQL Connectivity Errors

21.1 Troubleshooting Checklist

1. Verify that MySQL Server is running.
2. Check the hostname and port.
3. Confirm that the MySQL2 package is installed.
4. Verify the application username and password.
5. Check that the database and required tables exist.
6. Confirm that the database account has the required privileges.
7. Check SQL syntax and the number of parameter placeholders.
8. Review server and application logs for diagnostic details.
9. Confirm that connections are released correctly.
10. Check pool usage and MySQL connection limits.
11. Verify that transactions are committed or rolled back as intended.

22. Performance Optimization

Application performance depends on connection management, SQL query design, indexing, result-set size, network latency, and database capacity.

22.1 Use Connection Pools

Connection pools reduce repeated connection-establishment overhead and allow an application to manage concurrent database operations more predictably.

22.2 Retrieve Only Required Columns

SELECT employee_id, employee_name
FROM employees;

Selecting only the columns required by the application can reduce data transfer and memory usage compared with retrieving every column unnecessarily.

22.3 Use Appropriate Indexes

Indexes can speed up queries that filter or sort on indexed columns. However, indexes also consume storage and add overhead to data modifications. Review query plans and workload patterns before adding indexes.

22.4 Use Pagination

Returning every record from a large table may increase memory usage and response time. Pagination helps applications retrieve a manageable number of records per request.

const page = 1;
const pageSize = 20;
const offset = (page - 1) * pageSize;

const [rows] = await pool.execute(
    `SELECT employee_id, employee_name
     FROM employees
     ORDER BY employee_id
     LIMIT ? OFFSET ?`,
    [pageSize, offset]
);

Validate pagination values before using them. For large tables, keyset pagination may perform better than large OFFSET values, depending on the query and indexes.

22.5 Avoid Holding Connections Too Long

• Perform database operations promptly.
• Release manually acquired pooled connections in a finally block.
• Do not hold a transaction open while waiting for unrelated network requests.
• Avoid unnecessary queries within loops when a suitable batch query is possible.
• Monitor query duration and connection-pool usage.

23. Working with Database Results

MySQL2 returns query rows as JavaScript objects in its common query interfaces. Each object contains column values, allowing the application to access fields using their column names.

23.1 Accessing Query Results

const [rows] = await pool.execute(
    "SELECT employee_id, employee_name, department FROM employees"
);

for (const employee of rows) {
    console.log(employee.employee_id);
    console.log(employee.employee_name);
    console.log(employee.department);
}

23.2 Counting Records

const [rows] = await pool.execute(
    "SELECT COUNT(*) AS total FROM employees"
);

console.log("Employee count:", rows[0].total);

23.3 Handling NULL Values

MySQL NULL values are commonly represented as JavaScript null. Applications should account for optional database fields rather than assuming every column contains a value.

const [rows] = await pool.execute(
    "SELECT employee_id, employee_name, department FROM employees"
);

for (const employee of rows) {
    const department = employee.department ?? "Not assigned";
    console.log(employee.employee_name, department);
}

24. Testing a Node.js and MySQL Application

Testing helps verify that database connectivity and CRUD operations work correctly under both normal and failure conditions.

24.1 Functional Tests

• Confirm that the application connects using valid credentials.
• Verify that SELECT returns the expected records.
• Insert a new employee and verify the generated ID.
• Update an employee and confirm the new value.
• Delete an employee and confirm the intended result.
• Verify that duplicate email values are rejected.
• Test requests for nonexistent employee IDs.

24.2 Failure Tests

• Attempt to connect using an incorrect password.
• Use an invalid database name.
• Submit invalid employee IDs.
• Submit missing required fields.
• Attempt to insert duplicate unique values.
• Simulate database unavailability in a controlled test environment.
• Verify that errors do not expose credentials or internal database details.

24.3 Testing Tools

25. Practical Project: Employee Management API

Project Objective: Build a simple REST API using Node.js, Express.js, and MySQL. The API will manage employee records through CRUD operations.

25.1 Project Requirements

• Connect to the company_db database using MySQL2.
• Configure a reusable connection pool.
• Create an endpoint to add an employee.
• Create an endpoint to retrieve all employees.
• Create an endpoint to retrieve one employee by ID.
• Create an endpoint to update an employee.
• Create an endpoint to delete an employee.
• Use parameterized queries for SQL values.
• Validate incoming request data.
• Handle duplicate email values and database errors.
• Use environment variables for sensitive connection settings.

25.2 Installation Commands

mkdir employee-api
cd employee-api
npm init -y
npm install express mysql2

25.3 Suggested Files

employee-api/
|-- db.js
|-- server.js
|-- package.json
|-- .env
|-- .gitignore

Exclude .env from version control and configure the environment variables securely.

25.4 Example .gitignore

node_modules/
.env

25.5 Expected API Behavior

25.6 Project Learning Outcomes

After completing the project, learners should be able to configure database connectivity, execute SQL queries through Node.js, create API endpoints, validate user input, handle errors, and manage connections safely.

26. Common Mistakes to Avoid

• Using an incorrect hostname, port, database name, or password.
• Forgetting to install the MySQL2 package.
• Creating a new database connection for every request when a pool would be more suitable.
• Failing to release manually acquired pooled connections.
• Concatenating untrusted values into SQL strings.
• Hardcoding production database credentials.
• Using a MySQL administrator account for routine application operations.
• Ignoring errors returned by promises.
• Returning detailed SQL errors to API clients.
• Leaving transactions open after failures.
• Accepting unvalidated user input.
• Allowing unauthorized users to update or delete database records.
• Retrieving large datasets without pagination or a suitable fetching strategy.

27. Interview Questions and Answers

Q1. What is Node.js?

Answer: Node.js is a JavaScript runtime environment that allows developers to execute JavaScript outside the browser, including for backend applications and APIs.

Q2. How can Node.js connect to MySQL?

Answer: Node.js can connect through a compatible database driver, such as MySQL2, which provides APIs for establishing connections, executing SQL, retrieving results, and managing connections.

Q3. What is MySQL2?

Answer: MySQL2 is a MySQL client library for Node.js. It supports callbacks, promises, prepared statements, and connection pooling.

Q4. What is the purpose of createConnection()?

Answer: The createConnection() method creates an individual connection to the MySQL server.

Q5. What is a connection pool?

Answer: A connection pool maintains reusable database connections to reduce repeated connection-establishment overhead and manage concurrent database operations.

Q6. What is the difference between query() and execute() in MySQL2?

Answer: Both methods execute SQL statements. The execute() method is designed to use prepared statements with parameter binding, while query() supports general query execution and parameter formatting. Use the appropriate method based on the operation and driver documentation.

Q7. Why should parameterized queries be used?

Answer: Parameterized queries separate values from SQL syntax and help protect against SQL injection attacks.

Q8. What is the purpose of async and await?

Answer: They provide a readable way to work with promises and asynchronous operations, such as database queries.

Q9. Why is connection pooling useful in APIs?

Answer: APIs often handle repeated and concurrent requests. A connection pool reuses connections and helps control how many connections the application opens.

Q10. How do you handle errors in asynchronous database code?

Answer: Use try/catch/finally around awaited operations, log useful diagnostic information securely, and ensure connections are released or closed.

Q11. What does insertId represent?

Answer: In MySQL2's insert result, insertId can provide the generated auto-increment identifier for a suitable insert operation.

Q12. What does affectedRows represent?

Answer: It reports the number of rows affected by a statement according to the driver's result semantics.

Q13. Why should database credentials be stored in environment variables or a secret manager?

Answer: This reduces the risk of exposing credentials in source code, repositories, or shared files.

Q14. What is a transaction?

Answer: A transaction groups related database operations so they can be committed together or rolled back when an error occurs.

Q15. What is SQL injection?

Answer: SQL injection is a vulnerability in which untrusted input changes the meaning of an SQL statement. Parameterized queries help prevent this vulnerability.

Q16. How can a Node.js application retrieve a single employee?

Answer: It can execute a parameterized SELECT query using the employee ID and return the matching row, or a not-found response if no row exists.

Q17. Why should an application use a dedicated MySQL account?

Answer: A dedicated account can be granted only the permissions required by the application, limiting the impact of credential exposure.

Q18. How can Node.js and MySQL performance be improved?

Answer: Use connection pooling, efficient queries, suitable indexes, pagination, parameterized statements, and monitoring of query latency and connection usage.

28. Quick Revision Checklist

• Understand Node.js and MySQL architecture.
• Install Node.js, npm, and the MySQL2 package.
• Create a database, table, and dedicated application user.
• Configure database connection settings securely.
• Use createConnection() for direct connections when suitable.
• Use createPool() for reusable connections in repeated workloads.
• Execute SELECT, INSERT, UPDATE, and DELETE queries.
• Use async and await for promise-based operations.
• Use parameterized queries for values.
• Manage transactions with commit and rollback.
• Release manually acquired pooled connections.
• Handle exceptions and protect sensitive error details.
• Validate API inputs and enforce authorization.
• Monitor performance and test common failure scenarios.

29. Conclusion

Connecting MySQL with Node.js applications is an important skill for backend development. With a driver such as MySQL2, developers can create database connections, execute SQL statements, retrieve records, perform CRUD operations, and integrate database functionality into Express APIs.

Reliable applications should use parameterized queries, dedicated database accounts, secure credential management, appropriate connection pooling, and careful error handling. Transactions help maintain consistency across related operations, while query optimization and monitoring support performance as application workloads grow.

Practice these examples in a development environment before adapting them for production. Always validate input, enforce authorization, protect credentials, and test both successful operations and failure scenarios.

To continue learning, explore the MySQL Training Course, visit the JustAcademy website, or register for a course demo.

30. Additional Learning Resources

• Official MySQL Documentation
• MySQL2 Documentation
• MySQL2 GitHub Repository
• Official Node.js Documentation
• Official Express.js Documentation
• MySQL Prepared Statements Documentation